<?php

require_once('functions/pageLoad.php');

loginRequired();

$page_title = 'My account';

$user_session = explode("_", $_SESSION['user']);

// mobile verified

	$sql = "SELECT mobile, mobile_verified FROM users WHERE id = '".$user_session[0]."' LIMIT 0,1";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);

	if(strlen($rs['mobile']) > 0 && $rs['mobile_verified'] != '1'){

	$mobile_not_verified = '<div class="yellow_box"><p><span style="color: #cc0000; font-weight: bold;">Warning!</span><br />Your mobile phone number has not been verified, please <a href="/notification-settings">verify your mobile</a> to receive text messages.</p></div>';

	}
	
if($_SESSION['tradesman'] == '1'){

// tradesman id
	//Change By Vyas Ishan 24 May 2013 Task2 added the status field condition
	$sql = "SELECT tradesman.id, balance, subscription_expiration, lucky, active, radius, latitude, longitude FROM tradesman 
			LEFT JOIN subscriptions ON tradesman.subscription_id = subscriptions.id 
			LEFT JOIN addresses ON tradesman.address_id = addresses.id 
			WHERE tradesman.user_id = '".$user_session[0]."' AND subscriptions.status = '1' LIMIT 0,1";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);
	$tradesman_id = $rs['id'];
	$current_balance = $rs['balance'];
	$subscription_expiration = $rs['subscription_expiration'];
	$lucky_swine = $rs['lucky'];
	$tradesman_active = $rs['active'];
	$subscription_radius = $rs['radius'];
	$lat = $rs['latitude'];
	$long = $rs['longitude'];
	
	if(isset($_GET['confirm'])){
	
	// subscription confirmation
	//Change By Vyas Ishan 24 May 2013 Task2 added the status field condition
	$sql = "SELECT radius, cost FROM tradesman
			LEFT JOIN subscriptions ON tradesman.subscription_id = subscriptions.id
			WHERE subscriptions.status = '1' AND tradesman.id = '".$tradesman_id."'";
	$sub_query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$sub_rs=mysql_fetch_assoc($sub_query);
	$subscription_confirmation = '<div class="yellow_box"><p>Thank you, your subscription of &pound;'.$sub_rs['cost'].' per year has now been confirmed. You can now quote jobs within a '.$sub_rs['radius'].' mile radius.</p></div>';
	
	}

// member since

	$sql = "SELECT member_since FROM users WHERE id = '".$user_session[0]."' LIMIT 0,1";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);
	$member_since = $rs['member_since'];

// trades

	$sql = "SELECT trade_id FROM tradesman_to_trade WHERE tradesman_id = '".$tradesman_id."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);

	$i = 1;

	while($rs=mysql_fetch_assoc($query)){

	$or = ($i != $rows) ? ' OR ' : '' ;

	$trade_query.="trade_id = '".$rs['trade_id']."'".$or;

	$i++;

	}


// jobs

	// get highest radius
	//Change By Vyas Ishan 24 May 2013 Task2 added the status field condition
	$sql = "SELECT radius FROM subscriptions where status = '1' ORDER BY radius DESC LIMIT 0,1";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);
	$highest_radius = $rs['radius'];
	
	$sql = "SELECT jobs.id, title, trade, jobs.status AS job_status, quotes.status AS quote_status, quote, rating, quote_id, quotes.id AS tradesmans_quote_id, area, ROUND(SQRT(POW((69.1 * (".$lat." - addresses.latitude)), 2) + POW((53 * (".$long." - addresses.longitude)), 2)), 1) AS distance, lead_purchases.accepted, lead_purchases.declined, lead_cost, telephone, mobile 
		FROM jobs 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN addresses ON jobs.address_id = addresses.id 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN quotes ON jobs.id = quotes.job_id AND quotes.tradesman_id = '".$tradesman_id."' 
		LEFT JOIN reviews ON jobs.id = reviews.job_id AND reviews.tradesman_id = '".$tradesman_id."' 
		LEFT JOIN deleted_quotes ON jobs.id = deleted_quotes.job_id AND deleted_quotes.tradesman_id = '".$tradesman_id."' 
		LEFT JOIN job_to_tradesman ON jobs.id = job_to_tradesman.job_id AND job_to_tradesman.tradesman_id = '".$tradesman_id."' 
		LEFT JOIN lead_purchases ON jobs.id = lead_purchases.job_id AND lead_purchases.tradesman_id = '".$tradesman_id."' 
		LEFT JOIN users ON jobs.user_id = users.id 
		WHERE (".$trade_query.") AND (jobs.custom = '0' OR (jobs.custom = '1' AND job_to_tradesman.id IS NOT NULL)) AND ((jobs.status = 'Open' AND date_added < '".$member_since."' ) OR date_added >= '".$member_since."' ) AND deleted_quotes.id IS NULL AND jobs.user_id != '".$user_session[0]."' AND jobs.job_active = '1' 
		HAVING distance <= ".$highest_radius." 
		ORDER BY date_added DESC";

	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);

	if($rows > 0){

	$jobs_content='';

	$i = 1;

	while($rs=mysql_fetch_assoc($query)){

	$link = build_job_link($rs['trade'],$rs['area'],$rs['id']);

	// unanswered messages

	$sql = "SELECT messages.id FROM messages 
		LEFT JOIN replies ON messages.id = replies.message_id 
		WHERE messages.job_id = '".$rs['id']."' AND messages.tradesman_id = '".$tradesman_id."' AND replies.id IS NULL";
	$um_query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$um_rows = mysql_num_rows($um_query);
	$unanswered_messages = ($um_rows > 0) ? '<a href="'.$link.'" style="color: #cc0000;"><strong>'.$um_rows.' new messages</strong></a>' : '0 new messages' ;


		$highlight = (is_int($i / 2)) ? ' class="highlight"' : '' ;

		$quote = (strlen($rs['quote']) > 0) ? 'You\'ve quoted &pound;'.$rs['quote'] : '';
		$quote = (strlen($rs['quote']) == 0 && $rs['job_status'] == 'Open') ? '<a href="'.$link.'">Submit quote</a>' : $quote ;

		$quote_status = (strlen($rs['quote_status']) > 0) ? $rs['quote_status'] : 'Open to quotes' ;
		$quote_status =	(strlen($rs['rating']) > 0) ? '<img src="/images/'.$rs['rating'].'-star.gif" alt="'.($rs['rating'] / 2).'/5" />' : $quote_status;
		$quote_status = ($quote_status == 'Accepted') ? '<span style="color: #009900;">Quote accepted!</span><br /><a href="'.$link.'" style="color: #cc0000;">Contact customer</a>' : $quote_status ;

		$delete = ($rs['quote_status'] == 'Declined' || $rs['quote_status'] == 'Cancelled' || strlen($rs['rating']) > 0) ? '<a href="/delete_quote.php?id='.$rs['id'].'">Delete</a>' : '--' ;
		$telephone = (strlen($rs['telephone']) == 0) ? $rs['mobile'] : $rs['telephone'] ;
		
		// lead purchases
		if($rs['accepted'] == null){$lead = '<a href="/purchase-lead.php?id='.$rs['id'].'">Purchase lead</a> (&pound;'.$rs['lead_cost'].')';}
		elseif($rs['declined'] == '1'){	$lead = 'Declined' ;}
		else{$lead = ($rs['accepted'] == '1') ? '<a href="'.$link.'">Approved</a>' : 'Awaiting response' ;}

		if(strlen($quote) > 0){

		$jobs_content.='<tr'.$highlight.'><td class="alignLeft"><a href="'.$link.'">'.ucfirst($rs['title']).'</a><br /><strong>'.$rs['area'].'</strong></td><td>'.$quote.'</td><td>'.$lead.'</td><td>'.$unanswered_messages.'</td><td>'.$quote_status.'</td><td>'.$delete.'</td></tr>';

		$i++;

		}

	}

	}

	if(strlen($jobs_content) > 0){

	$jobs_content = '<table width="100%" class="standard">'.$jobs_content.'</table>';

	}

	else{
	
	$jobs_content = '<p>You do not currently have any jobs available to quote.</p>';

	}
	
	
	
	// visit requiests
	
	$sql = "SELECT jobs.id, trade, area, accepted, title FROM visit_requests 
			LEFT JOIN jobs ON visit_requests.job_id = jobs.id 
			LEFT JOIN trades ON jobs.trade_id = trades.id 
			LEFT JOIN areas ON jobs.area_id = areas.id 
			LEFT JOIN deleted_quotes ON visit_requests.job_id = deleted_quotes.job_id AND deleted_quotes.tradesman_id = '".$tradesman_id."'
			WHERE visit_requests.tradesman_id = '".$tradesman_id."' AND jobs.status = 'Open' AND deleted_quotes.id IS NULL";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);
	
	if($rows > 0){
	
	$jobs_content.='<h2>Your visit requests</h2><table width="100%" class="standard">';
	
	$i = 1;
	
	while($rs=mysql_fetch_assoc($query)){

	$link = build_job_link($rs['trade'],$rs['area'],$rs['id']);
	$highlight = (is_int($i / 2)) ? ' class="highlight"' : '' ;
	$accepted = ($rs['accepted'] == '1') ? 'Accepted' : 'Awaiting response' ;
	
	
	$jobs_content.='<tr'.$highlight.'><td class="alignLeft"><a href="'.$link.'">'.ucfirst($rs['title']).'</a></td><td style="text-align: right;">'.$accepted.'</td></tr>';
	
	$i++;
	
	}
	
	$jobs_content.='</table>';
	
	}

// feedback

$sql = "SELECT job_id, title, rating, review, date_reviewed, trade, area FROM reviews 
	LEFT JOIN jobs ON reviews.job_id = jobs.id 
	LEFT JOIN trades ON jobs.trade_id = trades.id 
	LEFT JOIN areas ON jobs.area_id = areas.id 
	WHERE tradesman_id = '".$tradesman_id."' ORDER BY date_reviewed DESC LIMIT 0,1";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
$feedback_rows = mysql_num_rows($query);

$feedback_content = '<h2 id="your_feedback_header">Your feedback</h2>';

if($feedback_rows > 0){

$feedback_rs=mysql_fetch_assoc($query);

$job_link = (strlen($feedback_rs['title']) > 0) ? '<a href="'.build_job_link($feedback_rs['trade'],$feedback_rs['area'],$feedback_rs['job_id']).'">'.ucfirst($feedback_rs['title']).'</a>' : '' ;

$feedback_content.='<div class="feedback_div">
'.$job_link.'
<p>'.ucfirst($feedback_rs['review']).'</p>
<p style="margin: 0px; padding: 0px;"><img src="/images/'.$feedback_rs['rating'].'-star.gif" alt="'.($feedback_rs['rating'] / 2).'/5" /></p>
<p style="margin-top: 0px; padding-top: 0px;"><small>'.date("jS F Y",strtotime($feedback_rs['date_reviewed'])).'</small></p>
</div>';

}

else{

$feedback_content.= '<p>You have not had any feedback yet.</p>';

}

$current_balance = ($current_balance > 0 && $lucky_swine != '1') ? '<div class="yellow_box"><p><span style="color: #cc0000; font-weight: bold;">Warning!</span><br />Your commissions are due and <strong>your account is locked</strong>, please make a payment of &pound;'.$current_balance.' to continue submitting quotes by <a href="/clear-balance"><strong>clicking here</strong></a></p></div>' : '' ;

if($lucky_swine != '1' && $tradesman_active == '1'){

// $subscription_reminder = (mktime() >= strtotime($subscription_expiration)) ? '<div class="yellow_box"><p><span style="color: #cc0000; font-weight: bold;">Warning!</span><br />Your subscription is due, please make a payment of &pound;'.$settings['membership'].' by <a href="/subscription-payment">clicking here</a></p></div>' : '<div class="yellow_box"><p>Your subscription is active and expires: <strong>'.date("j", strtotime($subscription_expiration)).'<sup>'.date("S", strtotime($subscription_expiration)).'</sup> '.date("M Y", strtotime($subscription_expiration)).'</strong></p></div>' ;

}

if($lucky_swine != '1' && $tradesman_active == '0' && mktime() >= strtotime($subscription_expiration)){

//header("Location: /subscription-payment");
//exit;

}

$content=$subscription_reminder.$current_balance.'
<p class="marginTopZero"><strong>Welcome to your account area. Here you can submit quotes, manage your company profile and post jobs.</strong></p>
<h2 id="your_quotes_header">Your quotes</h2>

'.$jobs_content;

}

else{

$content='<p class="marginTopZero"><strong>Welcome to your account area. Here you can post jobs, accept quotes and leave feedback for your tradesman.</strong></p>';

}

// user


	$sql = "SELECT jobs.id, title, area, trade, jobs.status, count(quotes1.id) AS num_quotes, quotes2.quote, rating, cancellation_reason, tradesman.company_name, tradesman.id AS tradesman_id, tradesman.username FROM jobs 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN deleted_jobs ON jobs.id = deleted_jobs.job_id 
		LEFT JOIN quotes AS quotes1 ON jobs.id = quotes1.job_id 
		LEFT JOIN quotes AS quotes2 ON jobs.quote_id = quotes2.id 
		LEFT JOIN tradesman ON quotes2.tradesman_id = tradesman.id 
		LEFT JOIN reviews ON jobs.id = reviews.job_id 
		WHERE jobs.user_id = '".$user_session[0]."' AND deleted_jobs.id IS NULL 
		GROUP BY jobs.id 
		ORDER BY date_added DESC";


	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);

	$jobs_content = '';

//	echo $user_session[0];

	if($rows > 0){

	$jobs_content ='<table width="100%" class="standard">';

	$i = 1;

	while($rs=mysql_fetch_assoc($query)){

	$link = build_job_link($rs['trade'],$rs['area'],$rs['id']);

	// unanswered questions

	$sql = "SELECT questions.id FROM questions 
		LEFT JOIN answers ON questions.id = answers.question_id 
		WHERE questions.job_id = '".$rs['id']."' AND answers.id IS NULL";
	$uq_query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$unanswered_questions = (mysql_num_rows($uq_query) > 0) ? '<a href="'.$link.'" style="color: #cc0000;"><strong>'.mysql_num_rows($uq_query).' new questions</strong></a>' : '0 new questions' ;

		$highlight = (is_int($i / 2)) ? ' class="highlight"' : '' ;

		$quotes = ($rs['num_quotes'] == 0) ? $rs['num_quotes'].' new quotes' : '<a href="'.$link.'"><strong>'.$rs['num_quotes'].' new quotes</strong></a>';
		$quotes = (strlen($rs['quote']) > 0) ? '&pound;'.$rs['quote'] : $quotes;

		$rating = ($rs['status'] == 'Closed' && strlen($rs['rating']) > 0) ? '<img src="/images/'.$rs['rating'].'-star.gif" alt="'.($rs['rating'] / 2).'/5" />' : '<a style="cursor:pointer; text-decoration:underline;" onclick="feedbackBox('.$rs['id'].')">Please leave feedback</a>';
		$rating = ($rs['status'] == 'Open' || $rs['status'] == 'Pending' || ($rs['cancellation_reason'] != '0' && strlen($rs['cancellation_reason']) > 0) || strlen($rs['status']) == 0) ? '--' : $rating;

		$cancel = (($rs['status'] == 'Closed' && strlen($rs['rating']) > 0) || ($rs['status'] == 'Closed' && $rs['cancellation_reason'] != '0' && strlen($rs['cancellation_reason']) > 0)) ? '<a href="/delete_job.php?id='.$rs['id'].'">Delete</a>' : '--';
		$cancel = ($rs['status'] == 'Open') ? '<a href="/cancel_job.php?id='.$rs['id'].'">Cancel</a>' : $cancel;

		$tradesman_link = ($rs['status'] == 'Closed' && strlen($rs['quote']) > 0) ? build_tradesman_link($rs['username'], $rs['tradesman_id']) : '';

		$status = ($rs['status'] == 'Closed' && strlen($rs['quote']) > 0) ? '<span style="color: #009900;">Quote accepted from:<br /><a href="'.$tradesman_link.'">'.$rs['company_name'].'</a></span>' : $rs['status'];
		$status = ($status == 'Pending') ? 'Pending approval' : $status ;
		$status = ($status == 'Open') ? 'Open to quotes' : $status ;

		$jobs_content.='<tr'.$highlight.'><td class="alignLeft"><a href="'.$link.'">'.ucfirst($rs['title']).'</a></td><td>'.$status.'</td><td>'.$quotes.'</td><td>'.$unanswered_questions.'</td><td>'.$rating.'</td><td>'.$cancel.'</td></tr>';
		$i++;

	}

		$jobs_content.='</table>';

	}

	else{
	
	$jobs_content = '<p>You do not currently have any open jobs, you can post a job anytime by <a href="/post-a-job">clicking here</a>.</p>';

	}

$content.='<h2 id="your_jobs_header">Your jobs</h2>
'.$jobs_content.'
'.$feedback_content.'
<!--<div class="yellow_box" style="margin-top: 10px;"><p><strong>Refer a friend!</strong></p><p>Know someone who may find our service useful? <a href="/refer-a-friend"><strong>Refer a friend today!</strong></a></p></div>-->
';

include('includes/meta.php');
include('includes/header.php');
include('includes/navigation.php');

?>

<h1>My account</h1>

<?php echo $subscription_confirmation.$mobile_not_verified.$content; ?>

<?php 

include('includes/rightColumn.php');
include('includes/footer.php');

?>